package com.peenongsaban.freelance.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import com.peenongsaban.freelance.bean.AptitudeTypeBean;
import com.peenongsaban.freelance.bean.PostJobBean;
import com.peenongsaban.freelance.bean.RequestFreelanceBean;
import com.peenongsaban.freelance.bean.RequestJobBean;
import com.peenongsaban.freelance.bean.StatusOfRequestBean;
import com.peenongsaban.freelance.connection.DatabaseConnectionPool;
import com.peenongsaban.freelance.dao.MyJobDAO;
import com.peenongsaban.freelance.meta.AptitudeTypeMeta;
import com.peenongsaban.freelance.meta.JobMeta;
import com.peenongsaban.freelance.meta.ListPostJobViewMeta;
import com.peenongsaban.freelance.meta.PostJobMeta;
import com.peenongsaban.freelance.meta.PostServiceMeta;
import com.peenongsaban.freelance.meta.RequestFreelanceMeta;
import com.peenongsaban.freelance.meta.RequestTypeMeta;
import com.peenongsaban.freelance.meta.StatusOfRequestMeta;
import com.peenongsaban.freelance.meta.UserMeta;
import com.peenongsaban.freelance.meta.ViewMeta;
import com.peenongsaban.freelance.model.MyJobModel;
import com.peenongsaban.freelance.utils.ConstantUtils;
import com.peenongsaban.freelance.utils.ExceptionUtil;
import com.peenongsaban.freelance.utils.StringUtils;

public class MyJobDAOImpl implements MyJobDAO {

	@Override
	public Vector<AptitudeTypeBean> listCategoryJob() {
		Vector<AptitudeTypeBean> vector = new Vector<AptitudeTypeBean>();
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		PreparedStatement statement = null;
		try {
			String sql = "SELECT J." + AptitudeTypeMeta.COL_APTITUDE_TYPE_ID
					+ ",J." + AptitudeTypeMeta.COL_APTITUDE_TYPE_NAME + ",J."
					+ AptitudeTypeMeta.COL_IMAGE_ICON + ",L.AMOUNT FROM "
					+ AptitudeTypeMeta.TABLE + " J LEFT JOIN (SELECT "
					+ ListPostJobViewMeta.COL_APTITUDE_TYPE_ID + ","
					+ ListPostJobViewMeta.COL_APTITUDE_TYPE_NAME + ","
					+ "COUNT(*) AS AMOUNT FROM "
					+ ListPostJobViewMeta.VIEW_CATEGORYTYPE + " GROUP BY "
					+ ListPostJobViewMeta.COL_APTITUDE_TYPE_ID + ") L ON J."
					+ AptitudeTypeMeta.COL_APTITUDE_TYPE_ID + " = L."
					+ ListPostJobViewMeta.COL_APTITUDE_TYPE_ID;

			statement = conn.prepareStatement(sql);
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				AptitudeTypeBean bean = new AptitudeTypeBean();
				bean.setAptitudeTypeID(rs
						.getInt(AptitudeTypeMeta.COL_APTITUDE_TYPE_ID));
				bean.setAptitudeTypeName(rs
						.getString(AptitudeTypeMeta.COL_APTITUDE_TYPE_NAME));
				bean.setImage_icon(rs
						.getString(AptitudeTypeMeta.COL_IMAGE_ICON));
				bean.setCountSumJob(rs.getInt("AMOUNT"));
				vector.add(bean);
			}
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			try {
				statement.close();
				databaseConnection.destroy();
				databaseConnection = null;
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return vector;
	}
	
	@Override
	public Vector<PostJobBean> listMyPostJob(MyJobModel model) {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		Vector<PostJobBean> vector = new Vector<PostJobBean>();
		PreparedStatement statement = null;
		boolean condition1 = false;
		try {
			String sql = "SELECT * FROM " + ViewMeta.LIST_POST_JOB_V
					+ " WHERE " + PostServiceMeta.COL_ID_CARD + " = ? ";
			if (!StringUtils.isEmptyOrNull(model.getOrderByPostJob())) {
				sql += " AND " + PostJobMeta.COL_STATUS_OF_POST + " = ? ORDER BY "+PostJobMeta.COL_POST_DATE+" DESC ";
				condition1 = true;
			}
			// Condition SQL command for paging
			int itemList = ConstantUtils.LIMIT_PAGING_MY_POST_JOB;
			if (model.getPagePostJob().getPageCurrentNo() == 0 || model.getPagePostJob().getPageCurrentNo() == 1) {
				sql += " LIMIT 0," + itemList;
			} else {
				sql += " LIMIT "
						+ ((model.getPagePostJob().getPageCurrentNo() - 1) * itemList)
						+ "," + itemList;
			}
			statement = conn.prepareStatement(sql);
			statement.setString(1, model.getUser().getIdCard());
			if (condition1) {
				statement.setString(2, model.getOrderByPostJob());
			}
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				PostJobBean bean = new PostJobBean();
				bean.setPostID(rs.getString(ListPostJobViewMeta.COL_POST_ID));
				bean.getUser().setIdCard(
						rs.getString(ListPostJobViewMeta.COL_ID_CARD));
				bean.setPostDate(rs.getDate(ListPostJobViewMeta.COL_POST_DATE));
				bean.setEndDateOfPost(rs
						.getDate(ListPostJobViewMeta.COL_END_DATE_OF_JOB));
				bean.getJob().setJobName(
						rs.getString(ListPostJobViewMeta.COL_JOB_NAME));
				bean.setStatusOfPost(rs
						.getString(ListPostJobViewMeta.COL_STATUS_OF_POST));
				bean.setAmountRequestPost(rs
						.getInt(ListPostJobViewMeta.COL_AMOUNT_USER_REQUEST_JOB));
				vector.add(bean);
			}
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			databaseConnection.destroy();
			databaseConnection = null;
			try {
				statement.close();
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return vector;
	}

	@Override
	public Vector<RequestJobBean> listMyRequestJob(MyJobModel model) {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		Vector<RequestJobBean> vector = new Vector<RequestJobBean>();
		PreparedStatement statement = null;
		boolean condition1 = false;
		try {
			String sql = "SELECT * FROM " + ViewMeta.LIST_MY_REQUEST_JOB_V
					+ " WHERE " + PostServiceMeta.COL_ID_CARD + " = ? AND "+RequestTypeMeta.COL_REQUEST_TYPE_ID+" IN (?,?,?,?) ";
			if (!StringUtils.isEmptyOrNull(model.getOrderByRequestJob())) {
				sql += " AND " + RequestTypeMeta.COL_REQUEST_TYPE_ID
						+ " = ? ORDER BY "+PostJobMeta.COL_POST_DATE+" DESC ";
				condition1 = true;
			}
			// Condition SQL command for paging
			int itemList = ConstantUtils.LIMIT_PAGING_MY_REQUEST_JOB;
			if (model.getPageRequestJob().getPageCurrentNo() == 0||model.getPageRequestJob().getPageCurrentNo() == 1) {
				sql += " LIMIT 0," + itemList;
			} else {
				sql += " LIMIT "
						+ ((model.getPageRequestJob().getPageCurrentNo() - 1) * itemList)
						+ "," + itemList;
			}
			statement = conn.prepareStatement(sql);
			statement.setString(1, model.getUser().getIdCard());
			statement.setInt(2, ConstantUtils.STATUS_WORKING_OF_REQUEST_JOB);
			statement.setInt(3, 5);
			statement.setInt(4, 6);
			statement.setInt(5, 7);
			if (condition1) {
				statement.setInt(6, Integer.parseInt(model.getOrderByRequestJob()));
			}
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				RequestJobBean bean = new RequestJobBean();
				bean.getPostJob().setPostID(
						rs.getString(PostJobMeta.COL_POST_ID));
				bean.getUser().setIdCard(rs.getString(PostJobMeta.COL_ID_CARD));
				bean.getPostJob().setPostDate(
						rs.getDate(PostJobMeta.COL_POST_DATE));
				bean.getPostJob().setEndDateOfPost(
						rs.getDate(JobMeta.COL_END_DATE_OF_JOB));
				bean.getPostJob().getJob()
						.setJobName(rs.getString(JobMeta.COL_JOB_NAME));

				Vector<StatusOfRequestBean> vecStatus = new Vector<StatusOfRequestBean>();
				StatusOfRequestBean statusBean = new StatusOfRequestBean();
				statusBean
				.getRequestType().setRequestTypeName(rs
						.getString(RequestTypeMeta.COL_REQUEST_TYPE_NAME));
				statusBean
						.setStatusOfRequestID(rs
								.getString(StatusOfRequestMeta.COL_STATUST_OF_REQUEST_ID));
				statusBean.setDateTime(rs
						.getDate(StatusOfRequestMeta.COL_DATE_TIME));
				vecStatus.add(statusBean);
				bean.setListStatusOfRequest(vecStatus);

				vector.add(bean);
			}
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			databaseConnection.destroy();
			databaseConnection = null;
			try {
				statement.close();
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return vector;
	}

	@Override
	public Vector<PostJobBean> listLastPostJob() {
		Vector<PostJobBean> vector = new Vector<PostJobBean>();
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		PreparedStatement statement = null;
		try {
			String sql = "SELECT " + ListPostJobViewMeta.COL_POST_ID + ","
					+ ListPostJobViewMeta.COL_JOB_NAME + " FROM "
					+ ListPostJobViewMeta.VIEW_CONDITON + " ORDER BY "
					+ ListPostJobViewMeta.COL_POST_DATE + " DESC LIMIT 0,9";

			statement = conn.prepareStatement(sql);
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				PostJobBean bean = new PostJobBean();
				bean.setPostID(rs.getString(ListPostJobViewMeta.COL_POST_ID));
				bean.getJob().setJobName(
						rs.getString(ListPostJobViewMeta.COL_JOB_NAME));

				vector.add(bean);
			}
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			try {
				statement.close();
				databaseConnection.destroy();
				databaseConnection = null;
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return vector;
	}

	@Override
	public int countLlistMyPostJob(MyJobModel model) {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		PreparedStatement statement = null;
		boolean condition1 = false;
		int size = 0;
		try {
			String sql = "SELECT COUNT(*) AS CNT FROM "
					+ ViewMeta.LIST_POST_JOB_V + " WHERE "
					+ PostServiceMeta.COL_ID_CARD + " = ? ";
			if (!StringUtils.isEmptyOrNull(model.getOrderByPostJob())) {
				sql += " AND " + PostJobMeta.COL_STATUS_OF_POST + " = ?";
				condition1 = true;
			}
			statement = conn.prepareStatement(sql);
			statement.setString(1, model.getUser().getIdCard());
			if (condition1) {
				statement.setString(2, model.getOrderByPostJob());
			}
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				size = rs.getInt("CNT");
			}
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			try {
				statement.close();
				databaseConnection.destroy();
				databaseConnection = null;
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return size;
	}

	@Override
	public int countLlistMyRequestJob(MyJobModel model) {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		PreparedStatement statement = null;
		boolean condition1 = false;
		int size = 0;
		try {
			String sql = "SELECT COUNT(*) AS CNT FROM "
					+ ViewMeta.LIST_MY_REQUEST_JOB_V + " WHERE "
					+ PostServiceMeta.COL_ID_CARD + " = ? AND "+RequestTypeMeta.COL_REQUEST_TYPE_ID+" IN (?,?,?,?) ";
			if (!StringUtils.isEmptyOrNull(model.getOrderByRequestJob())) {
				sql += " AND " + RequestTypeMeta.COL_REQUEST_TYPE_ID
						+ " = ?";
				condition1 = true;
			}
			statement = conn.prepareStatement(sql);
			statement.setString(1, model.getUser().getIdCard());
			statement.setInt(2, 4);
			statement.setInt(3, 5);
			statement.setInt(4, 6);
			statement.setInt(5, 7);
			if (condition1) {
				statement.setInt(6, Integer.parseInt(model.getOrderByRequestJob()));
			}
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				size = rs.getInt("CNT");
			}
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			try {
				statement.close();
				databaseConnection.destroy();
				databaseConnection = null;
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return size;
	}

	@Override
	public int countAllMyPostJob(String idCard) {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		PreparedStatement statement = null;
		int size = 0;
		try {
			String sql = "SELECT COUNT(*) AS CNT FROM "
					+ ViewMeta.LIST_POST_JOB_V + " WHERE "
					+ PostServiceMeta.COL_ID_CARD + " = ? ";
			statement = conn.prepareStatement(sql);
			statement.setString(1, idCard);
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				size = rs.getInt("CNT");
			}
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			try {
				statement.close();
				databaseConnection.destroy();
				databaseConnection = null;
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return size;
	}

	@Override
	public int countAllMyRequestJob(String idCard) {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		PreparedStatement statement = null;
		int size = 0;
		try {
			String sql = "SELECT COUNT(*) AS CNT FROM "
					+ ViewMeta.LIST_MY_REQUEST_JOB_V + " WHERE "
					+ PostServiceMeta.COL_ID_CARD + " = ? AND "+RequestTypeMeta.COL_REQUEST_TYPE_ID+" IN (?,?,?,?) ";
			statement = conn.prepareStatement(sql);
			statement.setString(1, idCard);
			statement.setInt(2, 4);
			statement.setInt(3, 5);
			statement.setInt(4, 6);
			statement.setInt(5, 7);
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				size = rs.getInt("CNT");
			}
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			try {
				statement.close();
				databaseConnection.destroy();
				databaseConnection = null;
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return size;
	}
	
	@Override
	public Vector<RequestJobBean> listRequestJobByRequestType(String idCard,
			int requestType) {
		Vector<RequestJobBean> vector = new Vector<RequestJobBean>();
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		PreparedStatement statement = null;
		try {
			String sql = "SELECT * FROM " + ViewMeta.LIST_MY_REQUEST_JOB_V
					+ " WHERE " + UserMeta.COL_ID_CARD + " = ? AND "
					+ RequestTypeMeta.COL_REQUEST_TYPE_ID + " = ? ";
			statement = conn.prepareStatement(sql);
			statement.setString(1, idCard);
			statement.setInt(2, requestType);
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				RequestJobBean bean = new RequestJobBean();
				bean.getPostJob().setPostID(
						rs.getString(PostJobMeta.COL_POST_ID));
				bean.getUser().setIdCard(rs.getString(PostJobMeta.COL_ID_CARD));
				bean.getPostJob().setPostDate(
						rs.getDate(PostJobMeta.COL_POST_DATE));
				bean.getPostJob().setEndDateOfPost(
						rs.getDate(JobMeta.COL_END_DATE_OF_JOB));
				bean.getPostJob().getJob()
						.setJobName(rs.getString(JobMeta.COL_JOB_NAME));

				Vector<StatusOfRequestBean> vecStatus = new Vector<StatusOfRequestBean>();
				StatusOfRequestBean statusBean = new StatusOfRequestBean();
				statusBean.getRequestType().setRequestTypeName(
						rs.getString(RequestTypeMeta.COL_REQUEST_TYPE_NAME));
				statusBean
						.setStatusOfRequestID(rs
								.getString(StatusOfRequestMeta.COL_STATUST_OF_REQUEST_ID));
				statusBean.setDateTime(rs
						.getDate(StatusOfRequestMeta.COL_DATE_TIME));
				statusBean.setResponseDateTime(rs
						.getDate(StatusOfRequestMeta.COL_RESPONSE_DATE_TIME));
				vecStatus.add(statusBean);
				bean.setListStatusOfRequest(vecStatus);

				vector.add(bean);
			}
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			try {
				statement.close();
				databaseConnection.destroy();
				databaseConnection = null;
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return vector;
	}

	@Override
	public Vector<RequestFreelanceBean> listBeEmployed(String idCard) {
		Vector<RequestFreelanceBean> vec = new Vector<RequestFreelanceBean>();
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		PreparedStatement statement = null;
		try {
			String sql = "SELECT * FROM " + ViewMeta.OWN_JOB_REQUEST_FREELANCE
					+ " WHERE " + RequestFreelanceMeta.COL_FREELANCE_ID_CARD
					+ " = ? AND " + RequestTypeMeta.COL_REQUEST_TYPE_ID
					+ " = ? ";
			statement = conn.prepareStatement(sql);
			statement.setString(1, idCard);
			statement.setInt(2, ConstantUtils.STATUS_REQUEST_FREELANCE);
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				RequestFreelanceBean bean = new RequestFreelanceBean();
				bean.getPostJob().setPostID(
						rs.getString(PostJobMeta.COL_POST_ID));
				bean.getPostJob().getJob()
						.setJobName(rs.getString(JobMeta.COL_JOB_NAME));

				Vector<StatusOfRequestBean> vecStatus = new Vector<StatusOfRequestBean>();
				StatusOfRequestBean statusBean = new StatusOfRequestBean();
				statusBean.setDateTime(rs
						.getDate(RequestFreelanceMeta.COL_REQUEST_DATE_TIME));
				vecStatus.add(statusBean);
				bean.setListStatusOfRequest(vecStatus);

				vec.add(bean);
			}
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			try {
				statement.close();
				databaseConnection.destroy();
				databaseConnection = null;
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return vec;
	}

}
